package com.yixun.ssm001;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellStyle;

import com.yixun.ssm001.admin.build.AdminProjectBuild;


public class PermissionsSqlByExcel {

	//excel中需要过滤掉的sheet
	private static Integer filterSheet[] = {0,1};
	/**
	 * 创建表
	 * @param filePath
	 * @param lastTableName
	 * @throws Exception
	 */
	public static void createSql() throws Exception{
		String tempSql="INSERT INTO `sys_tm_resources` (`resources_name`,`resources_code`, `parent_code`,  `resources_sort`, `resources_href`, `resources_icon`, `resources_level`, `resources_type`, `permission`, `remark`, `create_user_id`, `create_time`, `update_user_id`, `update_time`, `del_flag`)"; 
		tempSql=tempSql+" VALUES('%s','%s','%s','%s','%s','%s','%s','%s','%s','手动添加','1','2016-05-11 18:08:03','1','2016-05-11 18:08:09','0');";
		StringBuffer sb  = new StringBuffer();
		File file = new File("D:\\workplace\\house_marketing_ERP\\document\\8.2-permissionsExcel.xls");
		InputStream is = new FileInputStream(file);
		POIFSFileSystem fs = new POIFSFileSystem(is);
		//所有标签页
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		int sheetNum = wb.getNumberOfSheets();
		HSSFSheet sheet = null;
		for(int i=0; i<sheetNum; i++){
			sheet = wb.getSheetAt(i);
			if(sheet==null)continue;
			int rowNum = sheet.getLastRowNum();
			int rowSize=rowNum+1;
			HSSFRow row = null;
			for(int j=1; j<rowSize; j++){
				row = sheet.getRow(j);
				if(row== null)continue;
				String value_0 = getCellValue(row.getCell(0));
				String value_1 = getCellValue(row.getCell(1));
				String value_2 = getCellValue(row.getCell(2));
				String value_3 = getCellValue(row.getCell(3));
				String value_4 = getCellValue(row.getCell(4));
				String value_5 = getCellValue(row.getCell(5));
				String value_6 = getCellValue(row.getCell(6));
				String value_7 = getCellValue(row.getCell(7));
				String value_8 = getCellValue(row.getCell(8));
				sb.append(String.format(tempSql, value_0,value_1,value_2,value_3,value_4,value_5,value_6,value_8,value_7));
				sb.append("\n");
			}
		}
		System.out.println(sb);

	}
	
	/**
	 * 获取单元格数据
	 * @param cell
	 * @return
	 */
	private static String getCellValue(HSSFCell cell) {
		String cellValue = "";
		DecimalFormat df = new DecimalFormat("##.##");
		if(cell == null){
			return cellValue;
		}
		switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_STRING:
				cellValue = cell.getRichStringCellValue().getString().trim();
				break;
			case HSSFCell.CELL_TYPE_NUMERIC:
				if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式  
	                SimpleDateFormat sdf = null;  
	                if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {  
	                    sdf = new SimpleDateFormat("HH:mm");  
	                } else {// 日期  
	                    sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
	                }  
	                Date date = cell.getDateCellValue();  
	                cellValue = sdf.format(date);  
	            } else if (cell.getCellStyle().getDataFormat() == 58) {  
	                // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)  
	                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
	                double value = cell.getNumericCellValue();  
	                Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);  
	                cellValue = sdf.format(date);  
	            } else {  
	                double value = cell.getNumericCellValue();  
	                CellStyle style = cell.getCellStyle();  
	                DecimalFormat format = new DecimalFormat();  
	                String temp = style.getDataFormatString();  
	                // 单元格设置成常规  
	                if (temp.equals("General")) {  
	                    format.applyPattern("#.##");  
	                }  
	                cellValue = format.format(value);  
	            }  
				//cellValue = df.format(cell.getNumericCellValue()).toString();
				break;
			case HSSFCell.CELL_TYPE_BOOLEAN:
				cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
				break;
			case HSSFCell.CELL_TYPE_FORMULA:
				cellValue = cell.getCellFormula();
				break;
			default:
				cellValue = "";
		}
		return cellValue;
	}
	
	public static void main(String[] args) {
		try {
			createSql();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
